package com.ratingbay.console.naivequery;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.ArrayList;
import java.util.HashMap;
import java.math.BigDecimal;
import java.sql.Timestamp;
 





import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.EntityTransaction;

import com.ratingbay.console.model.Game;
import com.ratingbay.console.model.GameSummary;

public class TweetTimeNaiveQuery{
	private TweetTimeNaiveQuery(){
	}
	private static TweetTimeNaiveQuery self = null;
    public static TweetTimeNaiveQuery getInstance(){
    	if(null == self){
    		self = new TweetTimeNaiveQuery();
    	}
        return self;
    }


    //for cjob
    public List<Long> getAllIdGames(){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "SELECT id FROM game";
        Query query =  em.createNativeQuery(sql);
        
        return query.getResultList();
    } 

    public int getTweetCount(Long linkId){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "select tweet_count from link where id = " + linkId;
        Query query =  em.createNativeQuery(sql);

        int res = Integer.parseInt(String.valueOf(query.getSingleResult()));
        return res;
    }

    public String getCreateDate(Long idTweet){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "select create_date from tweet where id = " + idTweet;
        Query query =  em.createNativeQuery(sql);
        DateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.S");    
        String res = format.format(query.getSingleResult());
        	return res;
    }

    public Boolean getIsRetweet(Long idTweet){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "select is_retweet from tweet where id = " + idTweet;
        Query query =  em.createNativeQuery(sql);

        Boolean res = Boolean.valueOf(String.valueOf(query.getSingleResult()));
        return res;
    }
    
    public Long getRetweetCount(Long idTweet){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "select retweet_count from tweet where id = " + idTweet;
        Query query =  em.createNativeQuery(sql);

        Long res = (Long) query.getSingleResult();
        if(res!=null){
        	return res;
        }else{
        	return 0L;
        }
    }
    
    public Long getUserId(Long idTweet){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "select user_id from tweet where id = " + idTweet;
        Query query =  em.createNativeQuery(sql);

        Long res = (Long) query.getSingleResult();
        if(res!=null){
        	return res;
        }else{
        	return 0L;
        }
    }
    
    public int getSentimentType(Long idTweet){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "select sentiment_type from tweet where id = " + idTweet;
        Query query =  em.createNativeQuery(sql);

        int res = Integer.parseInt(String.valueOf(query.getSingleResult()));
        return res;
    }
    
    public BigDecimal getSentimentProbability(Long idTweet){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "select sentiment_probability from tweet where id = " + idTweet;
        Query query =  em.createNativeQuery(sql);

        BigDecimal res = (BigDecimal)query.getSingleResult();
        if(res!=null){
        	return res;
        }else{
        return null;
        }
    }
    
    public void insertTweet(Long gameId,Timestamp start,Timestamp end,Long idTweet,int tweetType){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();
    	String sql = "insert into rb_tweet (id_game,user_id,tweet_type,id,create_date,is_retweet,retweet_count,sentiment_type,sentiment_probability) values ("+ gameId + ","+getUserId(idTweet)+"," + tweetType + ","+ idTweet + ",'" + getCreateDate(idTweet) + "'," + getIsRetweet(idTweet) + "," + getRetweetCount(idTweet) + "," + getSentimentType(idTweet) + "," + getSentimentProbability(idTweet) +")"; 
		EntityTransaction tx = em.getTransaction();
		tx.begin();
		Query query = em.createNativeQuery(sql);
		query.executeUpdate();
		tx.commit();
    }
    
    public void updataTweet(Long idTweet){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();
    	String sql = "update rb_tweet set retweet_count = " + getRetweetCount(idTweet) + ",sentiment_probability = " + getSentimentProbability(idTweet) + " where id = " + idTweet;
		EntityTransaction tx = em.getTransaction();
		tx.begin();
		Query query = em.createNativeQuery(sql);
		query.executeUpdate();
		tx.commit();
    }
    
    public void deleteTweet(Long gameId,int tweetType,Timestamp start){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();
    	String sql;
    	if(tweetType==0){
    		sql= "delete from rb_tweet where id_game = " +gameId+ " and tweet_type = " + tweetType + " and id not in(select id from rb_tweet where id_game = "+gameId+" and tweet_type = "+tweetType+" and create_date > '"+start+"' order by retweet_count desc limit 500)";
    	}else if(tweetType==1){
    		sql= "delete from rb_tweet where id_game = " +gameId+ " and tweet_type = " + tweetType + " and id not in(select id from rb_tweet where id_game = "+gameId+" and tweet_type = "+tweetType+" and create_date > '"+start+"' order by sentiment_probability desc limit 500)";
    	}else if(tweetType==2){
    		sql= "delete from rb_tweet where id_game = " +gameId+ " and tweet_type = " + tweetType + " and id not in(select id from rb_tweet where id_game = "+gameId+" and tweet_type = "+tweetType+" and create_date > '"+start+"' order by sentiment_probability desc limit 500)";
    	}else{
    		sql= "delete from rb_tweet where id_game = " +gameId+ " and tweet_type = " + tweetType + " and id not in(select id from rb_tweet where id_game = "+gameId+" and tweet_type = "+tweetType+" and create_date > '"+start+"' order by create_date desc limit 500)";
    	}
		EntityTransaction tx = em.getTransaction();
		tx.begin();
		Query query = em.createNativeQuery(sql);
		query.executeUpdate();
		tx.commit();
    }
    
    public Boolean isTweetIn(Long idTweet){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "select count(*) from rb_tweet where id = " + idTweet;
        Query query =  em.createNativeQuery(sql);
        
        List<Long> res = (List<Long>) query.getResultList();
        if(res.get(0)!=0){
        		return true;
        	}else{
        		return false;
        	}
    }
    
    public Long countLink(Long gameId,int tweetType){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "select count(*) from rb_tweet where id_game = " + gameId + " and tweet_type = " + tweetType;
        
        Query query =  em.createNativeQuery(sql);
    	List<Long> res = (List<Long>) query.getResultList();
    	if(res.size() > 0){
        	return res.get(0);
        }
        return 0L;
    }
    
    public Long countTime(Long gameId,int tweetType,Timestamp start,Timestamp end){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "select count(*) from rb_tweet where id_game = " + gameId + " and tweet_type = " + tweetType + " and create_date < '"+start +"'";
        
        Query query =  em.createNativeQuery(sql);
    	List<Long> res = (List<Long>) query.getResultList();
    	if(res.size() > 0){
        	return res.get(0);
        }
        return 0L;
    }
    
    //cjob entry
    public void analysisTweetAndLink(List<Long> idGames,Date beginTime,Date curDate) {
        System.out.println("CJOB start analysisTweet");
        Timestamp start = new Timestamp(beginTime.getTime());
		Timestamp end = new Timestamp(curDate.getTime());
		EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        Query query =  null;
  
    	for(int i=0; i< idGames.size(); i++) {
    		Long gameId = idGames.get(i);
    		/*tweetType = 0 select popularTweet from tweet limit 500
    		 * tweetType = 1 select positiveTweet from tweet limit 500
    		 * tweetType = 2 select negativeTweet from tweet limit 500
    		 * tweetType = 3 select latestTweet from tweet limit 500
    		 */
    		for(int tweetType =0;tweetType<4;tweetType++){
    			System.out.println("rb_tweet game id is " + gameId + " gameId size is " + i);
			String tSql = "select count(*) from rb_tweet where id_game = " + gameId + " and tweet_type = " + tweetType;
			String qSql;
			if(tweetType==0){
				qSql = "select id from tweet where retweet_count is not null and is_retweet = false and id_game = "+gameId+" and create_date between '"+start+"' and '"+end+"' and id in (select min(id) from (select * from tweet where retweet_count is not null and is_retweet = false and id_game = "+gameId+" and create_date between '"+start+"' and '"+end+"' order by retweet_count desc limit 1000)t group by t.content order by max(t.retweet_count) desc) order by retweet_count desc limit 500";
			}else if(tweetType==1){
				qSql = "select id from tweet where sentiment_probability is not null and sentiment_type = "+tweetType+" and id_game = "+gameId+" and create_date between '"+start+"' and '"+end+"' and id in (select min(id) from (select * from tweet where sentiment_probability is not null and sentiment_type = "+tweetType+" and id_game = "+gameId+" and create_date between '"+start+"' and '"+end+"' order by sentiment_probability desc limit 1000)t group by t.content order by max(t.sentiment_probability) desc) order by sentiment_probability desc limit 500";
			}else if(tweetType==2){
				qSql = "select id from tweet where sentiment_probability is not null and sentiment_type = "+tweetType+" and id_game = "+gameId+" and create_date between '"+start+"' and '"+end+"' and id in (select min(id) from (select * from tweet where sentiment_probability is not null and sentiment_type = "+tweetType+" and id_game = "+gameId+" and create_date between '"+start+"' and '"+end+"' order by sentiment_probability desc limit 1000)t group by t.content order by max(t.sentiment_probability) desc) order by sentiment_probability desc limit 500";
			}else{
				qSql = "select id from tweet where id_game = "+gameId+" and create_date between '"+start+"' and '"+end+"' and id in (select min(id) from (select * from tweet where id_game = "+gameId+" and create_date between '"+start+"' and '"+end+"' order by create_date desc limit 1000)t group by t.content order by max(t.create_date) desc) order by create_date desc limit 500";
			}
			query = em.createNativeQuery(tSql);
			Long isInserted = (Long)query.getSingleResult();
			query = em.createNativeQuery(qSql);
			List<Long> tweetId = (List<Long>)query.getResultList();
			if(1 > isInserted){
				for(int j=0;j<tweetId.size();j++){
					Long idTweet = tweetId.get(j);
					insertTweet(gameId,start,end,idTweet,tweetType);
				}
			}else{
				for(int j=0;j<tweetId.size();j++){
					Long idTweet = tweetId.get(j);
					if(isTweetIn(idTweet)){
						updataTweet(idTweet);
					}else{
						insertTweet(gameId,start,end,idTweet,tweetType);
					}
				}
				if(countLink(gameId,tweetType)>500||countTime(gameId,tweetType,start,end)>0){
					deleteTweet(gameId,tweetType,start);
				}	
			}
    	}
		}
        System.out.println("CJOB end analysisTweet");
    }
}